package dba

import (
	"MBTIx/models"

	"github.com/jmoiron/sqlx"
)

func GetClassId(db *sqlx.DB, student *models.Student) {
	var class_id int
	db.Get(&class_id,
		`select class.class_id from class, department
		where class.class_name = $1
		and department.dep_name = $2
		and department.dep_id = class.dep_id`,
		student.Class_name, student.Dep_name)
	student.Class_id = class_id
}

func InsertStudent(db *sqlx.DB, stu_t models.Student) {
	GetClassId(db, &stu_t)
	db.NamedExec(
		`insert into student(stu_id,stu_name,result,class_id)
			values (:stu_id,:stu_name,'',:class_id)`,
		stu_t)
}

func GetAllStudent(db *sqlx.DB, students *[]models.Student) {
	db.Select(students, `select 
			t1.stu_id,t1.stu_name,t2.class_name,t3.dep_name,t4.col_name,t1.result
			from 
			student t1,class t2, department t3, college t4
			where
			t1.class_id = t2.class_id and
			t2.dep_id = t3.dep_id and
			t3.col_id = t4.col_id`)
}

func UpdateStudent(db *sqlx.DB, stu_t models.Student) {
	GetClassId(db, &stu_t)
	db.NamedExec(
		`update student set(stu_name,result,class_id) = (:stu_name,:result,:class_id)
		 where stu_id = :stu_id`, stu_t)
}

func DeleteStudent(db *sqlx.DB, stu_id string) {
	db.Exec(`delete from student where stu_id = $1`, stu_id)
}

func GetClassStudent(db *sqlx.DB, id string, students *[]models.Student) {
	db.Select(students,
		`select t1.stu_id, t1.stu_name, t1.result from student t1 where 
	class_id = (select t2.class_id from student t2 where t2.stu_id = $1) order by t1.stu_id`, id)
}

func GetStudent(db *sqlx.DB, id string, student *models.Student) {
	db.Get(student, `select t1.stu_id,t1.stu_name,t2.class_name,t3.dep_name,t4.col_name,t1.result
			from 
			student t1,class t2, department t3, college t4
			where
			t1.class_id = t2.class_id and
			t2.dep_id = t3.dep_id and
			t3.col_id = t4.col_id and
			stu_id = $1`, id)
}
